package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.TmsDetectInfoDto;

public class TmsDetectInfoImp extends ViewListDao implements TmsDetectInfoDao {

	protected final String[] sortKeyList = new String[]{"CARSERIAL","CARSERIAL","CARNAME","UPDATETIME"};//
	protected final String[] personSortKeyList = new String[]{"CARSERIAL","CARSERIAL","CARNAME","CARLICENSE","EXHAUST","COLOR","GEARBOX","CHKMODEL","UPDATETIME"};//待检测人工列表排序
	protected final String[] allocateSortKeyList = new String[]{"CARSERIAL","CARSERIAL","CARNAME","CARLICENSE","REGDATE","CARCHECKINSUR","TRAFFICINSUR","EXHAUST","COLOR","GEARBOX","fuelDict","NATURE","UPDATETIME"};//待分配人工列表排序
	protected final String[] waitIpadSortKeyList = new String[]{"CARSERIAL","CARSERIAL","CARNAME","CARLICENSE","EXHAUST","COLOR","GEARBOX","SOURCE"};//待确认IPAD列表
	protected final String[] ipadSortKeyList = new String[]{"CARSERIAL","CARSERIAL","CARNAME","CARLICENSE","MILEAGE","TOTALSCORE","EXTERNALSCORE","INTERNALSCORE","CHKMODEL","UPDATETIME"};//排序待检测IPADIPAD列表排序
	protected final String[] searchSortKeyList = new String[]{"CARSERIAL","CARSERIAL","CARNAME","CARLICENSE","EXHAUST","COLOR","GEARBOX","CHKMODEL","STATUS","UPDATETIME"};// 车辆搜索 
	
	public TmsDetectInfoImp(int pageNo, int maxIndex, int sortKey, boolean asc) throws TmsDaoException {
		super( pageNo,maxIndex, sortKey, asc);
	}

	public TmsDetectInfoImp() throws TmsDaoException {
		super();
	}

	/**
	 * 根据车辆状态查询属于什么位置
	 * 
	 */
	@Override
	public List<TmsDetectInfoDto> getAllocateDispatchInfo(int status) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsDetectInfoDto dto = null;
		List<TmsDetectInfoDto> list = new ArrayList<TmsDetectInfoDto>();

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT     				 ");
			sql.append("COUNT(*) AS COUNTS   	 ");
			sql.append("FROM   	 ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append("  AS DECT   	 						");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
			sql.append("  AS AUC     	 						");
			sql.append("  ON AUC.SERIAL = DECT.SERIAL	 	");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  AS MANAGE     	 					");
			sql.append("  ON MANAGE.SERIAL = DECT.SERIAL AND MANAGE.BIDSTATUS = 1	 ");				
			sql.append("  WHERE DECT.STATUS = ?  ");
			sql.append("  AND    MANAGE.BIDSTATUS=1  			");

			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, status);

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new TmsDetectInfoDto();
				dto.setCounts(rs.getInt("COUNTS"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return list;
	}
	/**
	 * 根据车辆ID查询出车辆的基本信息
	 * 
	 * @throws TmsDaoException
	 */
	@Override
	public TmsDetectInfoDto getAllocateCarInfoById(String carSerial,int status)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsDetectInfoDto dto = null;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT     		 ");
			sql.append("DECT.ID AS ID,     		 				");
			sql.append("DECT.SERIAL AS CARSERIAL, 		 	");
			sql.append("AUC.CARNAME AS CARNAME, 		 		");
			sql.append("AUC.REGDATE AS REGDATE,  		 		");
			sql.append("AUC.LICENSEPLATE AS CARLICENSE, 	 	");
			sql.append("AUC.YEARCAREFULPERIOD AS CARCHECKINSUR, ");
			sql.append("AUC.JQX AS TRAFFICINSUR,	 			");
			sql.append("AUC.DISPLACEMENT AS EXHAUST,         	");
			sql.append("AUC.COLOR AS COLOR,           			");
			sql.append("AUC.FUELTYPE AS FUELDICT,        		");
			sql.append("AUC.CARUSE AS NATURE,          			");
			sql.append("DECT.STATUS AS STATUS,          		");
			sql.append("AUC.GEARBOX AS GEARBOX,         		");
			sql.append("DECT.UPDATETIME  AS UPDATETIME,      	");
			sql.append("DECT.CHKMODEL AS CHKMODEL,        		");//检测模式和检测ID在待确认以后的状态才会出现
			sql.append("DECT.SKYLIGHT AS SKYLIGHT,     	 		");//--八大配置
			sql.append("DECT.LEATHERSEATS AS LEATHERSEATS,  	");
			sql.append("DECT.ALUMINUMRIM AS ALUMINUMRIM,     	");
			sql.append("DECT.GPSNAVIGATION AS GPSNAVIGATION,   	");
			sql.append("DECT.AUTOWINDOW AS AUTOWINDOW,      	");
			sql.append("DECT.ABS AS ABS,     		 			");  
			sql.append("DECT.SRSNUMBER AS SRSNUMBER,     	 	");
			sql.append("DECT.FOURWHEELDRIVE AS FOURWHEELDRIVE,  ");//--
			sql.append("DECT.CARDESCRIBE AS CARDESCRIBE,  		");
			sql.append("DECT.MILEAGE AS MILEAGE,  				");
			sql.append("DECT.CARDETECTID AS CARDETECTID,      	");
			sql.append("DECT.CHKTIME AS CHKTIME,      			");
			sql.append("MANAGE.BIDSTATUS AS BIDSTATUS      		");
			//sql.append("MAX(MANAGE.STARTTIME) AS MUPDATETIME    ");
			sql.append("FROM   	 								");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append("  AS DECT   	 						");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
			sql.append("  AS AUC     	 						");
			sql.append("  ON AUC.SERIAL = DECT.SERIAL	 	");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  AS MANAGE     	 					");
			sql.append("  ON MANAGE.SERIAL = DECT.SERIAL 	 ");				
			sql.append("  WHERE DECT.SERIAL="+carSerial+"   			");
			sql.append("  AND MANAGE.STARTTIME = (SELECT STARTTIME FROM tms_auc_management WHERE SERIAL = DECT.SERIAL )		");
			if(status == 1)
			{
				sql.append("  AND  MANAGE.BIDSTATUS="+status+" ");
			}

			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new TmsDetectInfoDto();
				dto.setID(rs.getLong("ID"));
				dto.setCarSerial(rs.getString("CARSERIAL"));
				dto.setCarName(rs.getString("CARNAME"));
				dto.setRegDate(rs.getString("REGDATE"));
				dto.setCarLicense(rs.getString("CARLICENSE"));
				dto.setCarCheckInsur(rs.getString("CARCHECKINSUR"));
				dto.setTrafficInsur(rs.getString("TRAFFICINSUR"));
				dto.setExhaust(rs.getInt("EXHAUST"));
				dto.setColor(rs.getString("COLOR"));
				dto.setFuelDict(rs.getInt("FUELDICT"));
				dto.setNature(rs.getInt("NATURE"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setGearbox(rs.getInt("GEARBOX"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				dto.setChkModel(rs.getInt("CHKMODEL"));
				dto.setCarDetectId(rs.getInt("CARDETECTID"));
				dto.setSkylight(rs.getInt("SKYLIGHT"));
				dto.setLeatherSeats(rs.getInt("LEATHERSEATS"));
				dto.setAluminumRim(rs.getInt("ALUMINUMRIM"));
				dto.setGpsNavigation(rs.getInt("GPSNAVIGATION"));
				dto.setAutoWindow(rs.getInt("AUTOWINDOW"));
				dto.setAbs(rs.getInt("ABS"));
				dto.setSrsNumber(rs.getInt("SRSNUMBER"));
				dto.setFourWheelDrive(rs.getInt("FOURWHEELDRIVE"));			
				dto.setMileage(rs.getInt("MILEAGE"));
				dto.setCarDescribe(rs.getString("CARDESCRIBE")); 
				dto.setChkTime(rs.getTimestamp("CHKTIME"));
				dto.setBidStatus(rs.getInt("BIDSTATUS")); 
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}

	/**
	 * 根据车辆来源和车辆状态查询出车辆列表信息
	 * 
	 * 11月20号改成根据车辆ID查询车辆信息
	 * 
	 * @throws SQLException 
	 * 
	 */
	@Override
	public List getAllocateDispatchInfoBySource(String serial, int status)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsDetectInfoDto dto = null;
		List<TmsDetectInfoDto> list = new ArrayList<TmsDetectInfoDto>();
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT     		 						");
			sql.append("DECT.ID AS ID,    		 				");
			sql.append("DECT.SERIAL AS CARSERIAL,			");
			sql.append("AUC.CARNAME AS CARNAME, 		 		");
			sql.append("AUC.REGDATE AS REGDATE,  		 		");
			sql.append("AUC.LICENSEPLATE AS CARLICENSE, 	 	");
			sql.append("AUC.YEARCAREFULPERIOD AS CARCHECKINSUR, ");
			sql.append("AUC.JQX AS TRAFFICINSUR,	 			");
			sql.append("AUC.DISPLACEMENT AS EXHAUST,         	");
			sql.append("AUC.COLOR AS COLOR,           			");
			sql.append("AUC.FUELTYPE AS FUELDICT,        		");
			sql.append("AUC.CARUSE AS NATURE,          			");
			sql.append("DECT.STATUS AS STATUS,          		");
			sql.append("AUC.GEARBOX AS GEARBOX,         		");
			sql.append("DECT.UPDATETIME  AS UPDATETIME,      	");
			sql.append("DECT.TOTALSCORE AS TOTALSCORE,      	");
			sql.append("DECT.EXTERNALSCORE AS EXTERNALSCORE,   	");
			sql.append("DECT.INTERNALSCORE AS INTERNALSCORE,   	");
			sql.append("DECT.CHKMODEL AS CHKMODEL,        		");
			sql.append("DECT.MILEAGE AS MILEAGE,    	 		");
			sql.append("DECT.CARDETECTID AS CARDETECTID 	 	");
			sql.append("FROM   	 								");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append("  AS DECT   	 						");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
			sql.append("  AS AUC     	 						");
			sql.append("  ON AUC.SERIAL = DECT.SERIAL	 	");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  AS MANAGE     	 					");
			sql.append("  ON MANAGE.SERIAL = DECT.SERIAL AND MANAGE.BIDSTATUS = 1	 ");				
			sql.append("  WHERE DECT.STATUS = "+status+"  ");
			sql.append("  AND    MANAGE.BIDSTATUS=1  			");
			//对车辆ID进行处理
			if(serial!=null && !"".equals(serial))
			{
				sql.append("  AND  DECT.SERIAL="+serial+"  	 ");
			}
	
			//根据状态判断用哪个排序数组
			if(status == 1)
			{
				//待分配 
				sql.append(getOrderBySql(allocateSortKeyList));
			}
			if(status == 3)
			{
				//待检测人工
				sql.append(getOrderBySql(personSortKeyList));
			}
			if(status == 5)
			{
				//待确认IPAD 
				sql.append(getOrderBySql(ipadSortKeyList));
			}
			
			try {	
				
			ps = conn.prepareStatement(getSql(sql.toString()));
			
			// 执行sql
			rs = ps.executeQuery();
				
			int i = 0;
			while (rs.next()) {
				dto = new TmsDetectInfoDto();
				dto.setID(rs.getLong("ID"));
				dto.setCarSerial(rs.getString("CARSERIAL"));
				dto.setCarName(rs.getString("CARNAME"));
				dto.setRegDate(rs.getString("REGDATE"));
				dto.setCarLicense(rs.getString("CARLICENSE"));
				dto.setCarCheckInsur(rs.getString("CARCHECKINSUR"));
				dto.setTrafficInsur(rs.getString("TRAFFICINSUR"));
				dto.setExhaust(rs.getInt("EXHAUST"));
				dto.setColor(rs.getString("COLOR"));
				dto.setFuelDict(rs.getInt("FUELDICT"));
				dto.setNature(rs.getInt("NATURE"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setGearbox(rs.getInt("GEARBOX"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				dto.setTotalScore(rs.getString("TOTALSCORE"));
				dto.setExternalScore(rs.getString("EXTERNALSCORE"));
				dto.setInternalScore(rs.getString("INTERNALSCORE"));
				dto.setChkModel(rs.getInt("CHKMODEL"));
				dto.setMileage(rs.getInt("MILEAGE"));
				dto.setCarDetectId(rs.getInt("CARDETECTID"));
				if(i%2 == 0){
					dto.setSeeParam(1);
				}else{
					dto.setSeeParam(2);
				}
				list.add(dto);
				i++;				
			}

			return list;
		
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	/**
	 * 根据车辆来源和车辆状态查询出车辆列表信息
	 * @throws SQLException 
	 * personSortKeyList
	 */
	@Override
	public List getWDPersonDispatchInfoBySource(int source, int status)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsDetectInfoDto dto = null;
		List<TmsDetectInfoDto> list = new ArrayList<TmsDetectInfoDto>();
		
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT     		 ");
			sql.append("ID,     		 ");
			sql.append("SERIAL, 		 ");
			sql.append("CARNAME, 		 ");
			sql.append("REGDATE,  		 ");
			sql.append("CARLICENSE, 	 ");
			sql.append("CARCHECKINSUR,   ");
			sql.append("TRAFFICINSUR,	 ");
			sql.append("EXHAUST,         ");
			sql.append("COLOR,           ");
			sql.append("FUELDICT,        ");
			sql.append("NATURE,          ");
			sql.append("STATUS,          ");
			sql.append("SOURCE,          ");
			sql.append("GEARBOX,         ");
			sql.append("UPDATETIME,      ");
			sql.append("TOTALSCORE,      ");
			sql.append("EXTERNALSCORE,   ");
			sql.append("INTERNALSCORE,   ");
			sql.append("CHKMODEL,        ");
			sql.append("MILEAGE    		 ");
			sql.append("FROM   	 ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append("    WHERE STATUS = "+status+" and  SOURCE="+source+"  ");
			
			sql.append(getOrderBySql(waitIpadSortKeyList));
			
			try {	
				
			ps = conn.prepareStatement(getSql(sql.toString()));
			
			// 执行sql
			rs = ps.executeQuery();
				
			int i = 0;
			while (rs.next()) {
				dto = new TmsDetectInfoDto();
				dto.setID(rs.getLong("ID"));
				dto.setCarSerial(rs.getString("SERIAL"));
				dto.setCarName(rs.getString("CARNAME"));
				dto.setRegDate(rs.getString("REGDATE"));
				dto.setCarLicense(rs.getString("CARLICENSE"));
				dto.setCarCheckInsur(rs.getString("CARCHECKINSUR"));
				dto.setTrafficInsur(rs.getString("TRAFFICINSUR"));
				dto.setExhaust(rs.getInt("EXHAUST"));
				dto.setColor(rs.getString("COLOR"));
				dto.setFuelDict(rs.getInt("FUELDICT"));
				dto.setNature(rs.getInt("NATURE"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setSource(rs.getInt("SOURCE"));
				dto.setGearbox(rs.getInt("GEARBOX"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				dto.setTotalScore(rs.getString("TOTALSCORE"));
				dto.setExternalScore(rs.getString("EXTERNALSCORE"));
				dto.setInternalScore(rs.getString("INTERNALSCORE"));
				dto.setChkModel(rs.getInt("CHKMODEL"));
				dto.setMileage(rs.getInt("MILEAGE"));
				if(i%2 == 0){
					dto.setSeeParam(1);
				}else{
					dto.setSeeParam(2);
				}
				list.add(dto);
				i++;				
			}

			return list;
		
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
     /**
      * csv数据入库
      */

	@Override
	public void carImportcsv(TmsDetectInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append("(");
			sql.append(" SERIAL, 	");
			sql.append(" CARNAME,   	");
			sql.append(" CARLICENSE, 	");
			sql.append(" REGDATE,   	");
			sql.append(" CARCHECKINSUR, ");
			sql.append(" TRAFFICINSUR, 	");
			sql.append(" EXHAUST,   	");
			sql.append(" COLOR,     	");
			sql.append(" GEARBOX,   	");
			sql.append(" FUELDICT,  	");
			sql.append(" NATURE,    	");
			sql.append(" SOURCE,    	");
			sql.append(" STATUS,    	");
			sql.append(" UPDATETIME, 	");
			sql.append(" UPDATEUSER  	");
			sql.append("  ) VALUES ( 	");
			sql.append(" ?,?,?,?,?,?,?,?,?,?,?,?,?,SYSDATE(),?)");
			
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getCarSerial());
			ps.setString(2, dto.getCarName());
			ps.setString(3, dto.getCarLicense());
			ps.setString(4, dto.getRegDate());
			ps.setString(5, dto.getCarCheckInsur());
			ps.setString(6, dto.getTrafficInsur());
			ps.setInt(7, dto.getExhaust());
			ps.setString(8, dto.getColor());
			ps.setInt(9, dto.getGearbox());
			ps.setInt(10, dto.getFuelDict());
			ps.setInt(11, dto.getNature());
			ps.setInt(12, dto.getSource());
			ps.setInt(13, dto.getStatus());
			ps.setInt(14, dto.getUpdateUser());
			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}

	}

	/**
	 * 删除车辆信息
	 * 
	 * @param dto
	 */
	@Override
	public void delCarinfo(String carSerial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("DELETE FROM  ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append(" WHERE  ");
			sql.append(" SERIAL=?  ");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, carSerial);

			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}

	}
     /**
      * 查询车辆数量
      */
	@Override
	public int getcarNum(int source,int carType) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT   ");
			sql.append(" COUNT(*) as NUM ");
			sql.append(" FROM     ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append(" WHERE     ");
			sql.append(" SOURCE=?  ");
			sql.append(" AND       ");
			sql.append(" STATUS=?  ");
			
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, source);
			ps.setInt(2, carType);
			rs =ps.executeQuery();
		
			while (rs.next()) {
				int i  = rs.getInt("NUM");
				return i;
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}

		return 0;

	}

	
	/**
	 * 根据车辆ID集合查询出车辆基本信息
	 * @throws TmsDaoException 
	 * 
	 */
	@Override
	public List<TmsDetectInfoDto> findAllocateCarInfoBySerialCollection(
			String serialCollection) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsDetectInfoDto dto = null;
		List<TmsDetectInfoDto> list = new ArrayList<TmsDetectInfoDto>();

			StringBuffer sql = new StringBuffer();//
			sql.append("SELECT     		 						");
			sql.append("DECT.ID AS ID,     		 				");
			sql.append("DECT.SERIAL AS CARSERIAL, 		 	");
			sql.append("AUC.CARNAME AS CARNAME, 		 		");
			sql.append("AUC.REGDATE AS REGDATE,  		 		");
			sql.append("AUC.LICENSEPLATE AS CARLICENSE, 	 	");
			sql.append("AUC.YEARCAREFULPERIOD AS CARCHECKINSUR, ");
			sql.append("AUC.JQX AS TRAFFICINSUR,	 			");
			sql.append("AUC.DISPLACEMENT AS EXHAUST,         	");
			sql.append("AUC.COLOR AS COLOR,           			");
			sql.append("AUC.FUELTYPE AS FUELDICT,        		");
			sql.append("AUC.CARUSE AS NATURE,          			");
			sql.append("DECT.STATUS AS STATUS,          		");
			sql.append("AUC.GEARBOX AS GEARBOX,         		");
			sql.append("DECT.UPDATETIME  AS UPDATETIME,      	");
			sql.append("DECT.TOTALSCORE  AS TOTALSCORE,      	");
			sql.append("DECT.EXTERNALSCORE  AS EXTERNALSCORE,   ");
			sql.append("DECT.INTERNALSCORE  AS INTERNALSCORE,   ");
			sql.append("DECT.CHKMODEL  AS CHKMODEL,        		");
			sql.append("DECT.MILEAGE  AS MILEAGE,    		 	");
			sql.append("DECT.CARDETECTID  AS CARDETECTID    		 	");
			sql.append("FROM   	 								");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append("  AS DECT   	 						");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
			sql.append("  AS AUC     	 						");
			sql.append("  ON AUC.SERIAL = DECT.SERIAL	 	");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  AS MANAGE     	 					");
			sql.append("  ON MANAGE.SERIAL = DECT.SERIAL AND MANAGE.BIDSTATUS = 1	 ");				
			sql.append("  WHERE  MANAGE.BIDSTATUS=1  			");
			sql.append("  AND DECT.SERIAL in "+serialCollection+"   ");

			try {	
				
			ps = conn.prepareStatement(getSql(sql.toString()));
			
			// 执行sql
			rs = ps.executeQuery();
				
			int i=0;
			while (rs.next()) {
				dto = new TmsDetectInfoDto();
				dto.setID(rs.getLong("ID"));
				dto.setCarSerial(rs.getString("CARSERIAL"));
				dto.setCarName(rs.getString("CARNAME"));
				dto.setRegDate(rs.getString("REGDATE"));
				dto.setCarLicense(rs.getString("CARLICENSE"));
				dto.setCarCheckInsur(rs.getString("CARCHECKINSUR"));
				dto.setTrafficInsur(rs.getString("TRAFFICINSUR"));
				dto.setExhaust(rs.getInt("EXHAUST"));
				dto.setColor(rs.getString("COLOR"));
				dto.setFuelDict(rs.getInt("FUELDICT"));
				dto.setNature(rs.getInt("NATURE"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setGearbox(rs.getInt("GEARBOX"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				dto.setTotalScore(rs.getString("TOTALSCORE"));
				dto.setExternalScore(rs.getString("EXTERNALSCORE"));
				dto.setInternalScore(rs.getString("INTERNALSCORE"));
				dto.setChkModel(rs.getInt("CHKMODEL"));
				dto.setMileage(rs.getInt("MILEAGE"));
				dto.setCarDetectId(rs.getInt("CARDETECTID"));
				if(i%2 == 0){
					dto.setSeeParam(1);
				}else{
					dto.setSeeParam(2);
				}
				list.add(dto);
				i++;
			}

			return list;
		
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 根据车辆ID集合和录入模式批量更新数据库
     * 
     * 实现车辆检测录入方式分配
	 * @throws TmsDaoException 
	 */
	@Override
	public void updateCarInfoBySerialCollectionAndAddMode(
			String serialCollection, String addMode,int userId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append(" SET ");
			sql.append(" STATUS = "+addMode+",  ");
			sql.append(" UPDATETIME = SYSDATE(),  ");
			sql.append(" UPDATEUSER = "+userId+"  ");
			sql.append(" WHERE  ");
			sql.append(" SERIAL IN "+serialCollection+" ");

			ps = conn.prepareStatement(sql.toString());
			
//			ps.setInt(1, Integer.parseInt(addMode));
//			ps.setInt(2, userId);

			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}			
		
	}
	/**
	 * 根据车辆ID集合批量删除车辆信息
     * 
     * 实现车辆待分配信息删除
	 * @throws TmsDaoException 
	 * @throws TmsDaoException 
	 */
	@Override
	public void deleteCarInfoBySerialCollection(String serialCollection) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("DELETE FROM  ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append(" WHERE  ");
			sql.append(" SERIAL IN "+serialCollection+"  ");
			ps = conn.prepareStatement(sql.toString());

			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	/**
	 * 根据车辆状态查询出所有待检测车辆信息
	 * 
	 * 关联TMS_AUC_CAR_INFO
	 * 
	 * 过滤掉竞价中和竞价结束的车辆信息
	 * 
	 * @throws TmsDaoException 
	 * 
	 */
	@Override
	public List<TmsDetectInfoDto> findAllWDIpadCarInfoByStatus(int status) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsDetectInfoDto dto = null;
		List<TmsDetectInfoDto> list = new ArrayList<TmsDetectInfoDto>();
			StringBuffer sql = new StringBuffer();//
			sql.append("SELECT     		 						");
			sql.append("DECT.ID AS ID,     		 				");
			sql.append("DECT.SERIAL AS CARSERIAL, 		 	");
			sql.append("AUC.CARNAME AS CARNAME, 		 		");
			sql.append("AUC.REGDATE AS REGDATE,  		 		");
			sql.append("AUC.LICENSEPLATE AS CARLICENSE, 	 		");
			sql.append("AUC.YEARCAREFULPERIOD AS CARCHECKINSUR,   	");
			sql.append("AUC.JQX AS TRAFFICINSUR,	 	");
			sql.append("AUC.DISPLACEMENT AS EXHAUST,         		");
			sql.append("AUC.COLOR AS COLOR,           			");
			sql.append("AUC.FUELTYPE AS FUELDICT,        		");
			sql.append("AUC.CARUSE AS NATURE,          			");
			sql.append("DECT.STATUS AS STATUS,          		");
			sql.append("AUC.GEARBOX AS GEARBOX,         		");
			sql.append("DECT.UPDATETIME  AS UPDATETIME       	");
			sql.append("FROM   	 ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append("  AS DECT   	 						");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
			sql.append("  AS AUC     	 						");
			sql.append("  ON AUC.SERIAL = DECT.SERIAL	 	");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  AS MANAGE     	 					");
			sql.append("  ON MANAGE.SERIAL = DECT.SERIAL AND MANAGE.BIDSTATUS = 1	 ");				
			sql.append("  WHERE  DECT.STATUS="+status+"  		");
			sql.append("  AND    MANAGE.BIDSTATUS=1  			");
			
			sql.append(getOrderBySql(sortKeyList));			

			try {	
				
			ps = conn.prepareStatement(getSql(sql.toString()));
			
			// 执行sql
			rs = ps.executeQuery();
				
			int i = 0;
			while (rs.next()) {
				dto = new TmsDetectInfoDto();
				dto.setID(rs.getLong("ID"));
				dto.setCarSerial(rs.getString("CARSERIAL"));
				dto.setCarName(rs.getString("CARNAME"));
				dto.setRegDate(rs.getString("REGDATE"));
				dto.setCarLicense(rs.getString("CARLICENSE"));
				dto.setCarCheckInsur(rs.getString("CARCHECKINSUR"));
				dto.setTrafficInsur(rs.getString("TRAFFICINSUR"));
				dto.setExhaust(rs.getInt("EXHAUST"));
				dto.setColor(rs.getString("COLOR"));
				dto.setFuelDict(rs.getInt("FUELDICT"));
				dto.setNature(rs.getInt("NATURE"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setGearbox(rs.getInt("GEARBOX"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				if(i%2 == 0){
					dto.setSeeParam(1);
				}else{
					dto.setSeeParam(2);
				}
				list.add(dto);
				i++;
			}

			return list;
		
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	/**
	 * 转录入模式的时候，如果有检测信息将基本信息表中所有检测信息置空
	 * @throws TmsDaoException 
	 * 
	 * 
	 */
	@Override
	public void updateCarInfoToZeroBySerial(String carSerial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append(" SET ");
			sql.append(" carDetectId = null,  ");
			sql.append(" chkModel = null,  ");
			sql.append(" skylight = null,  ");
			sql.append(" leatherSeats = null,  ");
			sql.append(" aluminumRim = null,  ");
			sql.append(" gpsNavigation = null,  ");
			sql.append(" autoWindow = null,  ");
			sql.append(" abs = null,  ");
			sql.append(" srsNumber = null,  ");
			sql.append(" fourWheelDrive =null,  ");
			sql.append(" totalScore = null,  ");
			sql.append(" externalScore = null,  ");
			sql.append(" internalScore = null,  ");
			sql.append(" troubleCar = null,  ");
			sql.append(" carDescribe =null,  ");
			sql.append(" chkTime = null,  ");
			sql.append(" UPDATETIME = SYSDATE(),  ");
			sql.append(" MILEAGE = null   ");//表显里程
			sql.append(" WHERE  ");
			sql.append(" SERIAL IN "+carSerial+" ");
			
			ps = conn.prepareStatement(sql.toString());
			
			ps.execute();
			
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}			
		
	}

	/**
	 * 
	 * 根据车辆ID更新车辆基本信息表中的检测信息
	 * 
	 * 车辆ID存在dto中
	 * @throws TmsDaoException 
	 */
	@Override
	public void updateCarDetectInfo(TmsDetectInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");//
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append(" SET ");
			sql.append(" CARDETECTID = ?,  ");
			sql.append(" CHKMODEL = ?,  ");
			sql.append(" SKYLIGHT = ?,  ");
			sql.append(" LEATHERSEATS = ?,  ");
			sql.append(" ALUMINUMRIM = ?,  ");
			sql.append(" GPSNAVIGATION = ?,  ");
			sql.append(" AUTOWINDOW = ?,  ");
			sql.append(" ABS = ?,  ");
			sql.append(" SRSNUMBER = ?,  ");
			sql.append(" FOURWHEELDRIVE =?,  ");
			sql.append(" TOTALSCORE = ?,  ");
			sql.append(" EXTERNALSCORE = ?,  ");
			sql.append(" INTERNALSCORE = ?,  ");
			sql.append(" TROUBLECAR = ?,  ");
			sql.append(" CARDESCRIBE =?,  ");
			sql.append(" STATUS =?,  ");
			sql.append(" MILEAGE =?,  ");
			sql.append(" CHKTIME = SYSDATE(),  ");
			sql.append(" UPDATETIME = SYSDATE(),  ");
			sql.append(" UPDATEUSER = ?  ");
			sql.append(" WHERE  ");
			sql.append(" SERIAL ="+dto.getCarSerial()+" ");
			
			ps = conn.prepareStatement(sql.toString());
			
			ps.setInt(1, dto.getCarDetectId());
			ps.setInt(2, dto.getChkModel());
			ps.setInt(3, dto.getSkylight());
			ps.setInt(4, dto.getLeatherSeats());
			ps.setInt(5, dto.getAluminumRim());
			ps.setInt(6, dto.getGpsNavigation());
			ps.setInt(7, dto.getAutoWindow());
			ps.setInt(8, dto.getAbs());
			ps.setInt(9, dto.getSrsNumber());
			ps.setInt(10, dto.getFourWheelDrive());
			ps.setString(11, dto.getTotalScore());
			ps.setString(12, dto.getExternalScore());
			ps.setString(13, dto.getInternalScore());
			ps.setInt(14, dto.getTroubleCar());
			ps.setString(15, dto.getCarDescribe());
			ps.setInt(16, dto.getStatus());
			ps.setInt(17, dto.getMileage());
			ps.setInt(18, dto.getUpdateUser());
			
			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}			
				
	}
	
	/**
	 * 更新车辆状态
	 * @param carSerial
	 * @param status
	 * @throws TmsDaoException
	 */
	@Override
	public void updateCarStatus(String carSerial, int status)
			throws TmsDaoException {
		PreparedStatement ps = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append(" SET ");
			sql.append(" STATUS = "+status+"  ");	
			sql.append(" WHERE  ");
			sql.append(" SERIAL ="+carSerial+" ");
			ps = conn.prepareStatement(sql.toString());
//			ps.setInt(1, status);
//			ps.setString(2, carSerial);		
			
			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(ps);
		}			
				
		
	}

	 /**
	  * 
	  * 根据车辆ID还原车辆基本信息，根据状态更新车辆状态
	 * @throws TmsDaoException 
	  */
	@Override
	public void resetCarInfoByIdAndStatus(String carSerial, int status,int userId) throws TmsDaoException {
		PreparedStatement ps = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append(" SET ");
			sql.append(" CARDETECTID = null,  	");//
			sql.append(" CHKMODEL = null,  		");
			sql.append(" MILEAGE = null,  		");//行驶里程
			sql.append(" SKYLIGHT = null,  		");
			sql.append(" LEATHERSEATS = null,  	");
			sql.append(" ALUMINUMRIM = null,  	");
			sql.append(" GPSNAVIGATION = null,  ");
			sql.append(" AUTOWINDOW = null,  	");
			sql.append(" ABS = null,  			");
			sql.append(" SRSNUMBER = null,  	");
			sql.append(" FOURWHEELDRIVE =null,  ");
			sql.append(" TOTALSCORE = null,  	");
			sql.append(" EXTERNALSCORE = null,  ");
			sql.append(" INTERNALSCORE = null,  ");
			sql.append(" TROUBLECAR = null,  	");
			sql.append(" CARDESCRIBE =null,  	");
			sql.append(" CHKTIME = null,  		");
			sql.append(" UPDATETIME = SYSDATE(),");
			sql.append(" STATUS = "+status+",  	");
			sql.append(" UPDATEUSER = "+userId+"  	");
			sql.append(" WHERE  ");
			sql.append(" SERIAL ="+carSerial+" ");
			
			ps = conn.prepareStatement(sql.toString());
//			ps.setInt(1, status);
//			ps.setString(2, carSerial);		
						
			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(ps);
		}			
		
	}

	/**
	 * 车辆搜索 根据车辆搜索条件查询车辆信息
	 * 
	 * 这块已完成车辆部分只传递一个状态6
	 * 
	 * 2013.11.20修改
	 * 
	 * @throws TmsDaoException 
	 * 
	 */
	@Override
	public List<TmsDetectInfoDto> findCarInfoByCon(TmsDetectInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null; 
		ResultSet rs = null;
		TmsDetectInfoDto rsDto = null;
		List<TmsDetectInfoDto> dtoList = new ArrayList<TmsDetectInfoDto>();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT     		 						");
			sql.append("DECT.ID AS ID,     		 				");
			sql.append("DECT.SERIAL AS CARSERIAL, 		 	");
			sql.append("AUC.CARNAME AS CARNAME, 		 		");
			sql.append("AUC.REGDATE AS REGDATE,  		 		");
			sql.append("AUC.LICENSEPLATE AS CARLICENSE, 	 	");
			sql.append("AUC.YEARCAREFULPERIOD AS CARCHECKINSUR, ");
			sql.append("AUC.JQX AS TRAFFICINSUR,	 			");
			sql.append("AUC.DISPLACEMENT AS EXHAUST,         	");
			sql.append("AUC.COLOR AS COLOR,           			");
			sql.append("AUC.FUELTYPE AS FUELDICT,        		");
			sql.append("AUC.CARUSE AS NATURE,          			");
			sql.append("DECT.STATUS AS STATUS,          		");
			sql.append("AUC.GEARBOX AS GEARBOX,         		");
			sql.append("DECT.UPDATETIME  AS UPDATETIME,      	");
			sql.append("DECT.CHKMODEL AS CHKMODEL,        		");//检测模式和检测ID在待确认以后的状态才会出现
			sql.append("DECT.SKYLIGHT AS SKYLIGHT,     	 		");//--八大配置
			sql.append("DECT.LEATHERSEATS AS LEATHERSEATS,  	");
			sql.append("DECT.ALUMINUMRIM AS ALUMINUMRIM,     	");
			sql.append("DECT.GPSNAVIGATION AS GPSNAVIGATION,   	");
			sql.append("DECT.AUTOWINDOW AS AUTOWINDOW,      	");
			sql.append("DECT.ABS AS ABS,     		 			");  
			sql.append("DECT.SRSNUMBER AS SRSNUMBER,     	 	");
			sql.append("DECT.FOURWHEELDRIVE AS FOURWHEELDRIVE,  ");//--
			sql.append("DECT.CARDESCRIBE AS CARDESCRIBE,  		");
			sql.append("DECT.MILEAGE AS MILEAGE,  				");
			sql.append("DECT.CARDETECTID AS CARDETECTID      	");
			//sql.append("MAX(MANAGE.STARTTIME) as MUPDATETIME      	");
			sql.append("FROM   	 								");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append("  AS DECT   	 						");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
			sql.append("  AS AUC     	 						");
			sql.append("  ON AUC.SERIAL = DECT.SERIAL	 	");
			sql.append("  LEFT JOIN   	 						");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  AS MANAGE     	 					");
			sql.append("  ON MANAGE.SERIAL = DECT.SERIAL 	    ");		//AND MANAGE.BIDSTATUS = 1		
			sql.append("  WHERE MANAGE.STARTTIME = (SELECT STARTTIME FROM tms_auc_management WHERE SERIAL = DECT.SERIAL )		");
						
			if(dto.getStatus()>0)//车辆状态
			{
				if(dto.getStatus() == 2)
				{
					sql.append(" AND (DECT.STATUS =2 OR DECT.STATUS =4)      ");
				}
				else{
					sql.append(" AND DECT.STATUS ="+dto.getStatus()+"      ");
				}
			}

			sql.append(getOrderBySql(searchSortKeyList));		
			try {
				
			ps = conn.prepareStatement(getSql(sql.toString()));

			// 执行sql
			rs = ps.executeQuery();

			int i = 0;
			while (rs.next()) {
				if(rs.getLong("ID") > 0)
				{
				rsDto = new TmsDetectInfoDto();
				rsDto.setID(rs.getLong("ID"));
				rsDto.setCarSerial(rs.getString("CARSERIAL"));
				rsDto.setCarName(rs.getString("CARNAME"));
				rsDto.setRegDate(rs.getString("REGDATE"));
				rsDto.setCarLicense(rs.getString("CARLICENSE"));
				rsDto.setCarCheckInsur(rs.getString("CARCHECKINSUR"));
				rsDto.setTrafficInsur(rs.getString("TRAFFICINSUR"));
				rsDto.setExhaust(rs.getInt("EXHAUST"));
				rsDto.setColor(rs.getString("COLOR"));
				rsDto.setFuelDict(rs.getInt("FUELDICT"));
				rsDto.setNature(rs.getInt("NATURE"));
				rsDto.setStatus(rs.getInt("STATUS"));
				rsDto.setGearbox(rs.getInt("GEARBOX"));
				rsDto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				rsDto.setChkModel(rs.getInt("CHKMODEL"));
				rsDto.setCarDetectId(rs.getInt("CARDETECTID"));
				rsDto.setSkylight(rs.getInt("SKYLIGHT"));
				rsDto.setLeatherSeats(rs.getInt("LEATHERSEATS"));
				rsDto.setAluminumRim(rs.getInt("ALUMINUMRIM"));
				rsDto.setGpsNavigation(rs.getInt("GPSNAVIGATION"));
				rsDto.setAutoWindow(rs.getInt("AUTOWINDOW"));
				rsDto.setAbs(rs.getInt("ABS"));
				rsDto.setSrsNumber(rs.getInt("SRSNUMBER"));
				rsDto.setFourWheelDrive(rs.getInt("FOURWHEELDRIVE"));			
				rsDto.setMileage(rs.getInt("MILEAGE"));
				rsDto.setCarDescribe(rs.getString("CARDESCRIBE")); 
				if(i%2 == 0){
					rsDto.setSeeParam(1);
				}else{
					rsDto.setSeeParam(2);
				}
				dtoList.add(rsDto);
				i++;
				}
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dtoList;
	}

	/**
	 * 
	 * 根据车辆ID查询车辆是否有已完成检测的检测报告
	 * 
	 */
	@Override
	public TmsDetectInfoDto getDetectinfoBySerialAndStatus(String serial)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsDetectInfoDto dto = null;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT     		 ");
			sql.append("CARDETECTID      	 ");
			sql.append("FROM   	 								 ");
			sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
			sql.append("  WHERE  SERIAL="+serial+" 				 ");
			sql.append("  AND  	 STATUS=6        				 ");

			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new TmsDetectInfoDto();
				dto.setCarDetectId(rs.getInt("CARDETECTID"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}
	
	/**
	 * 查看已完成的基本检测报告评分信息
	 */
	@Override
	public TmsDetectInfoDto getDetectScore(String serial) throws TmsDaoException{
	PreparedStatement ps = null;
	ResultSet rs = null;
	TmsDetectInfoDto dto = null;

	try {
		conn = super.getConnection();

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT     		 ");
		sql.append("*      	 ");
		sql.append("FROM   	 								 ");
		sql.append(TmsTableNames.TMS_DECT_CAR_INFO);
		sql.append("  WHERE  SERIAL="+serial+" 				 ");
		sql.append("  AND  	 STATUS=6        				 ");
		sql.append("   AND  	 CARDETECTID=1    ");
		ps = conn.prepareStatement(sql.toString());
		// 执行sql
		rs = ps.executeQuery();
		while (rs.next()) {
			dto = new TmsDetectInfoDto();
		 dto.setTotalScore(rs.getString("TOTALSCORE"));
		 dto.setExternalScore(rs.getString("EXTERNALSCORE"));
		 dto.setInternalScore(rs.getString("INTERNALSCORE"));
		}
	} catch (SQLException e) {
		throw new TmsDaoException(e.toString());

	} finally {
		super.close(rs, ps);
	}
	return dto;
}
	
	
}
